package com.sz.netty.service.impl;

import com.mybatisflex.core.service.IService;
import com.mybatisflex.spring.service.impl.ServiceImpl;
import com.sz.netty.entity.DtuFtpMessageDetail;
import com.sz.netty.mapper.NettyDtuFtpMessageDetailMapper;
import com.sz.netty.service.DtuFtpMessageDetailService;
import org.springframework.stereotype.Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import lombok.extern.slf4j.Slf4j;

/**
 * DTU FTP消息详情服务实现类
 */
@Slf4j
@Service
public class NettyDtuFtpMessageDetailServiceImpl extends ServiceImpl<NettyDtuFtpMessageDetailMapper, DtuFtpMessageDetail> implements DtuFtpMessageDetailService {
    
    @Autowired
    private DataSource dataSource;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public DtuFtpMessageDetail saveMessageDetail(DtuFtpMessageDetail messageDetail) {
        // 设置创建时间和更新时间
        Date now = new Date();
        if (messageDetail.getCreateTime() == null) {
            messageDetail.setCreateTime(now);
        }
        messageDetail.setUpdateTime(now);
        
        // 如果时间是时间戳0，设置为null
        if (messageDetail.getTime() != null && messageDetail.getTime().getTime() == 0) {
            messageDetail.setTime(null);
        }
        
        // 保存到数据库
        this.save(messageDetail);
        return messageDetail;
    }
    
    @Override
    public DtuFtpMessageDetail saveMessageDetailToDeviceTable(DtuFtpMessageDetail messageDetail, String deviceId) {
        // Validate device ID before proceeding
        if (!isValidDeviceId(deviceId)) {
            log.warn("Invalid device ID detected: '{}'. Skipping save to device-specific table.", deviceId);
            return messageDetail;
        }
        
        // 设置创建时间和更新时间
        Date now = new Date();
        if (messageDetail.getCreateTime() == null) {
            messageDetail.setCreateTime(now);
        }
        messageDetail.setUpdateTime(now);
        
        // 如果时间是时间戳0，设置为null
        if (messageDetail.getTime() != null && messageDetail.getTime().getTime() == 0) {
            messageDetail.setTime(null);
        }
        
        // 构建设备特定的表名
        String deviceTable = "dtu_ftp_message_detail_" + deviceId;
        
        // 检查表是否存在，如果不存在则创建
        if (!isTableExists(deviceTable)) {
            createDeviceTable(deviceTable);
        }
        
        // 保存到设备特定的表中
        saveToDeviceTable(messageDetail, deviceTable);
        
        return messageDetail;
    }
    
    /**
     * Validate device ID to prevent creating tables with invalid names
     * @param deviceId the device ID to validate
     * @return true if valid, false otherwise
     */
    private boolean isValidDeviceId(String deviceId) {
        // Check if device ID is null or empty
        if (deviceId == null || deviceId.isEmpty()) {
            return false;
        }
        
        // Check if device ID contains replacement characters () which indicate binary data interpreted as text
        if (deviceId.contains("")) {
            return false;
        }
        
        // Check if device ID contains only valid ASCII characters (alphanumeric and underscore)
        // This prevents issues with special characters, binary data, and non-ASCII characters in table names
        for (int i = 0; i < deviceId.length(); i++) {
            char c = deviceId.charAt(i);
            if (!((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= '0' && c <= '9') || c == '_')) {
                return false;
            }
        }
        
        // Additional check for control characters which might indicate binary data
        for (int i = 0; i < deviceId.length(); i++) {
            char c = deviceId.charAt(i);
            if (c < 32 || c == 127) { // Control characters and DEL
                return false;
            }
        }
        
        return true;
    }
    
    /**
     * 检查表是否存在
     * @param tableName 表名
     * @return 是否存在
     */
    private boolean isTableExists(String tableName) {
        // First validate the table name
        if (!isValidTableName(tableName)) {
            log.warn("Invalid table name detected: '{}'. Skipping table existence check.", tableName);
            return true; // Return true to prevent table creation
        }
        
        try {
            String sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, tableName);
            return count != null && count > 0;
        } catch (Exception e) {
            return false;
        }
    }
    
    /**
     * Validate table name to prevent SQL injection and invalid table names
     * @param tableName the table name to validate
     * @return true if valid, false otherwise
     */
    private boolean isValidTableName(String tableName) {
        // Check if table name is null or empty
        if (tableName == null || tableName.isEmpty()) {
            return false;
        }
        
        // Check if table name contains replacement characters () which indicate binary data interpreted as text
        if (tableName.contains("")) {
            return false;
        }
        
        // Check if table name contains only valid ASCII characters
        for (int i = 0; i < tableName.length(); i++) {
            char c = tableName.charAt(i);
            if (!((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= '0' && c <= '9') || c == '_')) {
                return false;
            }
        }
        
        // Check for control characters
        for (int i = 0; i < tableName.length(); i++) {
            char c = tableName.charAt(i);
            if (c < 32 || c == 127) { // Control characters and DEL
                return false;
            }
        }
        
        // Check if table name starts with the valid prefix
        return tableName.startsWith("dtu_ftp_message_detail_");
    }
    
    /**
     * 创建设备特定的表
     * @param tableName 表名
     */
    private void createDeviceTable(String tableName) {
        // Validate table name before creating
        if (!isValidTableName(tableName)) {
            log.warn("Invalid table name detected: '{}'. Skipping table creation.", tableName);
            return;
        }
        
        String sql = "CREATE TABLE " + tableName + " (" +
                "id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID', " +
                "ftp_message_log_id BIGINT COMMENT '关联的FTP消息日志ID', " +
                "time DATETIME COMMENT '时间信息', " +
                "rand_time_sort VARCHAR(255) COMMENT '随机时间序列', " +
                "status_bit VARCHAR(255) COMMENT '状态位', " +
                "positioning_status TINYINT(1) COMMENT '定位状态', " +
                "alarm_power_status TINYINT(1) COMMENT '电源报警状态', " +
                "vibration_status TINYINT(1) COMMENT '震动状态', " +
                "radar_distance DOUBLE COMMENT '雷达距离', " +
                "longitude DECIMAL(10,6) COMMENT '经度', " +
                "latitude DECIMAL(10,6) COMMENT '纬度', " +
                "speed DECIMAL(10,2) COMMENT '速度', " +
                "direction INT COMMENT '方向', " +
                "satellite_num INT COMMENT '卫星数量', " +
                "horizontal_accuracy_factor DECIMAL(10,2) COMMENT '水平精度因子', " +
                "message_num INT COMMENT '数据记录条数', " +
                "message_content LONGBLOB COMMENT '报文内容（完整报文）', " +
                "message LONGBLOB COMMENT '单条记录数据', " +
                "create_time DATETIME COMMENT '创建时间', " +
                "update_time DATETIME COMMENT '更新时间', " +
                "PRIMARY KEY (id), " +
                "INDEX idx_ftp_message_log_id (ftp_message_log_id), " +
                "INDEX idx_time (time) " +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='DTU FTP消息详情表_" + tableName.replace("dtu_ftp_message_detail_", "") + "'";
        
        jdbcTemplate.execute(sql);
    }
    
    /**
     * 保存到设备特定的表中
     * @param messageDetail 消息详情实体
     * @param tableName 表名
     */
    private void saveToDeviceTable(DtuFtpMessageDetail messageDetail, String tableName) {
        // Validate table name before saving
        if (!isValidTableName(tableName)) {
            log.warn("Invalid table name detected: '{}'. Skipping data save.", tableName);
            return;
        }
        
        String sql = "INSERT INTO " + tableName + " (" +
                "ftp_message_log_id, time, rand_time_sort, status_bit, positioning_status, " +
                "alarm_power_status, vibration_status, radar_distance, longitude, latitude, " +
                "speed, direction, satellite_num, horizontal_accuracy_factor, message_num, " +
                "message_content, message, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        // Use KeyHolder to capture the generated ID
        org.springframework.jdbc.support.KeyHolder keyHolder = new org.springframework.jdbc.support.GeneratedKeyHolder();
        
        jdbcTemplate.update(new org.springframework.jdbc.core.PreparedStatementCreator() {
            @Override
            public java.sql.PreparedStatement createPreparedStatement(java.sql.Connection connection) throws java.sql.SQLException {
                java.sql.PreparedStatement ps = connection.prepareStatement(sql, java.sql.Statement.RETURN_GENERATED_KEYS);
                ps.setObject(1, messageDetail.getFtpMessageLogId());
                ps.setObject(2, messageDetail.getTime());
                ps.setObject(3, messageDetail.getRandTimeSort());
                ps.setObject(4, messageDetail.getStatusBit());
                ps.setObject(5, messageDetail.getPositioningStatus() != null ? (messageDetail.getPositioningStatus() ? 1 : 0) : null);
                ps.setObject(6, messageDetail.getAlarmPowerStatus() != null ? (messageDetail.getAlarmPowerStatus() ? 1 : 0) : null);
                ps.setObject(7, messageDetail.getVibrationStatus() != null ? (messageDetail.getVibrationStatus() ? 1 : 0) : null);
                ps.setObject(8, messageDetail.getRadarDistance());
                ps.setObject(9, messageDetail.getLongitude());
                ps.setObject(10, messageDetail.getLatitude());
                ps.setObject(11, messageDetail.getSpeed());
                ps.setObject(12, messageDetail.getDirection());
                ps.setObject(13, messageDetail.getSatelliteNum());
                ps.setObject(14, messageDetail.getHorizontalAccuracyFactor());
                ps.setObject(15, messageDetail.getMessageNum());
                ps.setObject(16, messageDetail.getMessageContent());
                ps.setObject(17, messageDetail.getMessage());
                ps.setObject(18, messageDetail.getCreateTime());
                ps.setObject(19, messageDetail.getUpdateTime());
                return ps;
            }
        }, keyHolder);
        
        // Set the generated ID back to the messageDetail object
        Number key = keyHolder.getKey();
        if (key != null) {
            messageDetail.setId(key.longValue());
        }
    }
}